package com.led.datas;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;

import net.sf.json.JSONArray;

import com.led.datatype.DataBasic;
import com.led.datatype.UserInfo;
import com.led.db.DBMgr;

public class DatasBasicAccess {

	static final String SQL_QUERY_DATAS_BASIC_BY_IDX_DATAS = 
			"select d.id, d.idx_datas, u.name as userName, d.info "
			+ " from datas_basic d, userInfo u"
			+ " where d.idx_datas = ? and d.userId = u.id limit 100";
	
	//see all, include myself, can seeOther is depose by java code
	static final String SQL_QUERY_DATAS_BASIC_ALL = 
			"select d.id, d.idx_datas, u.name as userName, d.info "
			+ " from datas_basic d, userInfo u"
			+ " where d.userId = u.id and "
			                + "( u.canOtherSee = 1 or u.id = ?) limit 100";
	//see only myself
	static final String SQL_QUERY_DATAS_BASIC_BY_USERID = 
			"select d.id, d.idx_datas, u.name as userName, d.info "
			+ " from datas_basic d, userInfo u"
			+ " where userId = ? and d.userId = u.id limit 100";

	public static DataBasic getDatasBasicByIdxDatas(int idxDatas){
		Connection con = DBMgr.getConnect();
		if(null == con){
			System.out.println("when getDatasBasicByIdxDatas, con is null!");
		}

		DataBasic dataBasic = null;
		ResultSet rs =null;
		PreparedStatement ps = null;
		try {
			ps =  con.prepareStatement(SQL_QUERY_DATAS_BASIC_BY_IDX_DATAS);
			ps.setInt(1, idxDatas);
			rs = ps.executeQuery();
			if(null == rs || rs.isAfterLast() || !rs.next()){
				System.out.println("no datas exists in getDatasBasicByIdxDatas");
				return dataBasic;
			}
			dataBasic = new DataBasic(rs);
		} catch (SQLException e) {
			e.printStackTrace();
			DBMgr.reset();
		} finally{
			if(null != rs){
				try{rs.close();} catch(Exception e){}
			}
			if(null != ps){
				try {ps.close();} catch (SQLException e) {e.printStackTrace();}
			}
		}
		return dataBasic;
	}	

	public static String getDatasBasic(UserInfo userInfo){
		Connection con = DBMgr.getConnect();
		if(null == con){
			System.out.println("when getDatasBasic, con is null!");
		}

		JSONArray js = new JSONArray();
		ResultSet rs =null;
		PreparedStatement ps = null;
		try {
			if(1 == userInfo.getCanSeeOthers()){
				ps =  con.prepareStatement(SQL_QUERY_DATAS_BASIC_ALL);
			} else {
				ps = con.prepareStatement(SQL_QUERY_DATAS_BASIC_BY_USERID);
			} 
			ps.setInt(1,  userInfo.getId());
			rs = ps.executeQuery();
			if(null == rs){
				System.out.println("no datas exists in getDatasBasic");
				return "";
			}

			LinkedList<DataBasic> dataList = DataBasic.getDatas(rs);
			for(DataBasic ld: dataList){
				js.add(ld.toJsonStr());
			} 
		} catch (SQLException e) {
			e.printStackTrace();
			DBMgr.reset();
		} finally{
			if(null != rs){
				try{rs.close();} catch(Exception e){}
			}
			if(null != ps){
				try {ps.close();} catch (SQLException e) {e.printStackTrace();}
			}
		}
		return js.toString();
	}


}
